MySQL的配置方法以及数据库配置常见错误及其解决方法

第一部分.MySQL的配置方法：

以下为笔者整理的mysql 5.5 安装配置教程笔记。通过逐一界面分析，解决大家在配置MySQL数据库时可能存在的疑惑。

首先给出MySQL下载地址：MySQL :: Download MySQL Installer

1、进入的是安装引导界面

2、然后进入的是类型选择界面，这里有3个类型：Typical（典型）、Complete（完全）、Custom（自定义）。这里建议 选择“自定义”（Custom）安装，这样可以自定义选择MySQL的安装目录，然后点“Next”下一步，出现自定义安装界面，为了数据安全起见，不建议将MySQL安装系统盘C目录。

3、准备安装，此步骤为自动进行，进行过后自动跳转至下一步。

4、安装完成之后会出现MySQL配置的引导界面

5、这里有个引导配置MySQL的选项，建议勾选

6、这里是配置MySQL的 MySQL配置的类型，可以选择两种配置类型：Detailed Configuration（详细配置）和Standard Configuration（标准配置）。

Standard Configuration（标准配置）选项适合想要快速启动MySQL而不必考虑服务器配置的新用户。Detailed Configuration（详细配置）选项适合想要更加细粒度控制服务器配置的高级用户。     

如果你是MySQL的新手，需要配置为单用户开发机的服务器，Standard类型即可满足需求。

、 

7、这里是配置MySQL的 MySQL服务器类型，可以选择3种服务器类型，选择哪种服务器将影响到MySQL Configuration Wizard（配置向导）对内存、硬盘和过程或使用的决策。

具体介绍如下：

 Developer Machine(开发机器)：该选项代表典型个人用桌面工作站。假定机器上运行着多个桌面应用程序。将MySQL服务器配置成使用最少的系统资源。

Server Machine（服务器）：该选项代表服务器，MySQL服务器可以同其它应用程序一起运行，例如FTP、email和web服务器。MySQL服务器配置成使用适当比例的系统资源。

 Dedicated MySQL Server Machine（专用MySQL服务器）：该选项代表只运行MySQL服务的服务器。假定运行没有运行其它应用程序。MySQL服务器配置成使用所有可用系统资源。

8、这里是配置MySQL的 数据库使用情况，你可以指出创建MySQL表时使用的表处理器。通过该选项，你可以选择是否使用InnoDB储存引擎，以及InnoDB占用多大比例的服务器资源。

Multifunctional Database（多功能数据库）：选择该选项，则同时使用InnoDB和MyISAM储存引擎，并在两个引擎之间平均分配资源。建议经常使用两个储存引擎的用户选择该选项。

Transactional Database Only（只是事务处理数据库）：该选项同时使用InnoDB和MyISAM 储存引擎，但是将大多数服务器资源指派给InnoDB储存引擎。建议主要使用InnoDB只偶尔使用MyISAM的用户选择该选项。

Non-Transactional Database Only（只是非事务处理数据库）：该选项完全禁用InnoDB储存引擎，将所有服务器资源指派给MyISAM储存引擎。建议不使用InnoDB的用户选择该选项。

9、这里是配置MySQL的 InnoDB表空间，有些用户可能想要将InnoDB表空间文件放到不同的位置，而不放到MySQL服务器数据目录。如果你的系统有较大的空间或较高性能的储存设备（例如RAID储存系统），则最好将表空间文件单独放到一个位置。

10、这里是配置MySQL的并发连接，限制所创建的与MySQL服务器之间的并行连接数量很重要，以便防止服务器耗尽资源。可以选择服务器的使用方法，并根据情况限制并行连接的数量。还可以手动设置并行连接的限制。

Decision Support（决策支持）(DSS)/OLAP：如果服务器不需要大量的并行连接可以选择该选项。假定最大连接数目设置为100,平均并行连接数为20。

Online Transaction Processing（联机事务处理）(OLTP)：如果你的服务器需要大量的并行连接则选择该选项。最大连接数设置为500。

 Manual Setting（人工设置）：选择该选项可以手动设置服务器并行连接的最大数目。从前面的下拉框中选择并行连接的数目，如果你期望的数目不在列表中，则在下拉框中输入最大连接数。

这里根据个人所需自行选择。

11、这里是配置MySQL的网络选项，可以启用或禁用TCP/IP网络，并配置用来连接MySQL服务器的端口号。默认情况启用TCP/IP网络。要想禁用TCP/IP网络，取消选择Enable TCP/IP Networking选项旁边的检查框。默认使用3306端口。要想更访问MySQL使用的端口，从下拉框选择一个新端口号或直接向下拉框输入新的端口号。如果你选择的端口号已经被占用，将提示确认选择的端口号，如果其他机器需要访问该数据库，建议勾选 Add firewall exception for this port（添加到防火墙例外）。

12、这里是配置MySQL的字符集，MySQL服务器支持多种字符集，可以设置适用于所有表、列和数据库的默认服务器字符集。可以通过Character Set（字符集对话框）来更改 MySQL服务器的默认字符集。

 Standard Character Set（标准字符集）：如果想要使用Latin1做为默认服务器字符集，则选择该选项。Latin1用于英语和许多西欧语言。

Best Support For Multilingualism（支持多种语言）：如果想要使用UTF8做为默认服务器字符集，则选择该选项。UTF8可以 将不同语言的字符储存为单一的字符集。

Manual Selected Default Character Set/Collation（人工选择的默认字符集/校对规则）：如果想要手动选择服务器的默认字符集，请选择该项。从下拉列表中选择期望的字符集。

13、这里是配置MySQL的服务选项，可以将MySQL服务器安装成服务。安装成服务，系统启动时可以自动启动MySQL服务器，甚至出现服务故障时可以随Windows自动启动。

默认情况，MySQL Configuration Wizard（配置向导）将MySQL服务器安装为服务，服务名为MySQL。如果你不想安装服务，取消Install As Windows Service选项旁边的选择框。可以从下拉框选择新的服务名或在下拉框输入新的服务名来更改服务名。要想将MySQL服务器安装为服务，但是不自动启动，不选中Launch the MySQL Server Automatically选项旁边的检查框。

14、这里是配置MySQL的安全选项，强烈建议为你的MySQL服务器设置一个root密码，默认情况MySQL Configuration Wizard（配置向导）要求你设置一个root密码。如果你不想设置root密码，不选中Modify Security Settings（修改安全设定值）选项旁边的选择框。要想设置root密码，在New root password(输入新密码)和Confirm（确认）两个框内输入期望的密码。如果重新配置已有的服务器，你还需要Current root password（当前root密码）框内输入已有的root密码。要想防止通过网络以root登录，选中Root may only connect from localhost（只允许从本机登陆连接root）选项旁边的框。这样可以提高root账户的安全。要想创建一个匿名用户账户，选中Create An Anonymous Account（创建匿名账户）选项旁边的框。创建匿名账户会降低服务器的安全，并造成登录和许可困难。

 用户需要牢记自己的密码，若不慎忘记，下方有对应解决方法。

15、最后一步，确认，点击Excute完成安装

第二部分.数据库配置常见错误及其解决方法：

一.Too many connections

(连接数过多，导致连接不上数据库，业务无法正常进行)

问题还原：

解决问题的思路：

1、首先先要考虑在我们 MySQL 数据库参数文件里面，对应的 max_connections 这个参数值是不是设置的太小了，导致客户端连接数超过了数据库所承受的最大值。

但这样调整会有隐患，因为我们无法确认数据库是否可以承担这么大的连接压力，就好比原来一个人只能吃一个馒头，但现在却非要让他吃 10 个，他肯定接受不了。反应到服务器上面，就有可能会出现宕机的可能。

所以这又反映出了，我们在新上线一个业务系统的时候，要做好压力测试。保证后期对数据库进行优化调整。

2、其次可以限制 InnoDB的并发处理数量，如果 innodb_thread_concurrency = 0(这种代表不受限制) 可以先改成 16 或是 64 看服务器压力。

如果非常大，可以先改的小一点让服务器的压力下来之后，然后再慢慢增大，根据自己的业务而定，个人建议可以先调整为 16 即可。

MySQL 随着连接数的增加性能是会下降的，在 MySQL 5.7 之前都需要让开发配合设置 thread pool，连接复用。MySQL 5.7 之后数据库自带 thread pool 了，连接数问题也得到了相应的解决。

另外对于有的监控程序会读取 information_schema 下面的表，可以考虑关闭下面的参数：

二.MySQL安装过程中的报错

解决思路：遇到这样的报错信息，我们要学会时时去关注错误日志 error log 里面的内容。看见了关键的报错点Permission denied，证明当前 MySQL 数据库的数据目录没有权限。

解决方法：

启动成功。

如何避免这类问题，个人建议在安装 MySQL 初始化的时候，一定加上–user=mysql，这样就可以避免权限问题。

三.数据库密码忘记的问题

我们有可能刚刚接手别人的 MySQL 数据库，而且没有完善的交接文档。root 密码可以丢失或者忘记了。

解决思路：目前是进入不了数据库的情况，所以我们要考虑是不是可以跳过权限。因为在数据库中，MySQL 数据库中 user 表记录着我们用户的信息。

解决方法：启动 MySQL 数据库的过程中，可以这样执行：

这样启动，就可以不用输入密码，直接进入 MySQL 数据库了。然后在修改你自己想要改的 root 密码即可。

四.truncate 删除数据，导致自动清空自增 ID，前端返回报错 not found

这个问题的出现，就要考虑下 truncate 和 delete 的区别了，看下实验演练：

首先先创建一张表：

插入三条数据：

先用 delete 进行删除全表信息，再插入新值。

结果发现 truncate 把自增初始值重置了，自增属性从 1 开始记录了。当前端用主键 id 进行查询时，就会报没有这条数据的错误。

个人建议不要使用 truncate 对表进行删除操作，虽然可以回收表空间，但是会涉及自增属性问题。这些坑，我们不要轻易钻进去。

五.阿里云 MySQL 的配置文件

阿里云 MySQL 的配置文件中，需要注意一个参数设置就是：

如果报你小写的表名找不到，那你就把远端数据库的表名改成小写，反之亦然。注意 Mybatis 的 Mapper 文件的所有表名也要相应修改。

六.数据库总会出现中文乱码的情况

有同学经常会问，为什么我的数据库总会出现中文乱码的情况。一堆中文乱码不知道怎么回事?当向数据库中写入创建表，并插入中文时，会出现这种问题。此报错会涉及数据库字符集的问题。

解决思路：对于中文乱码的情况，记住老师告诉你的三个统一就可以。还要知道在目前的 MySQL 数据库中字符集编码都是默认的 UTF8。

处理办法：

Emoji 表情符号录入 MySQL 数据库中报错：

解决思路：针对表情插入的问题，一定还是字符集的问题。

处理方法：我们可以直接在参数文件中，加入：

注：utf8mb4 是 utf8 的超集。

七.使用 binlog_format=statement 这种格式，跨库操作，导致从库丢失数据，用户访问导致出现错误数据信息

当前数据库二进制日志的格式为：binlog_format=statement

在主库设置 binlog-do-db=mydb1(只同步mydb1这一个库)。

在主库执行 use mydb2;

这条语句不会同步到从库。

但是这样操作就可以;

因为这是在同一个库中完成的操作。

在生产环境中建议使用binlog的格式为row，而且慎用 binlog-do-db 参数。

八.MySQL 数据库连接超时的报错

大多数做 DBA 的同学，可能都会被开发人员告知，你们的数据库报了这个错误了，赶紧看看是哪里的问题。

这个问题是由两个参数影响的，wait_timeout 和 interactive_timeout。

数据默认的配置时间是 28800(8小时)意味着，超过这个时间之后，MySQL 数据库为了节省资源，就会在数据库端断开这个连接，MySQL 服务器端将其断开了，但是我们的程序再次使用这个连接时没有做任何判断，所以就挂了。

解决思路：先要了解这两个参数的特性，这两个参数必须同时设置，而且必须要保证值一致才可以。

我们可以适当加大这个值，8 小时太长了，不适用于生产环境。因为一个连接长时间不工作，还占用我们的连接数，会消耗我们的系统资源。

解决方法：可以适当在程序中做判断，强烈建议在操作结束时更改应用程序逻辑以正确关闭连接，然后设置一个比较合理的 timeout 的值(根据业务情况来判断)。

九.can’t open file (errno:24)

有的时候，数据库跑得好好的，突然报不能打开数据库文件的错误了。

解决思路：首先我们要先查看数据库的 error log。然后判断是表损坏，还是权限问题。还有可能磁盘空间不足导致的不能正常访问表;操作系统的限制也要关注下;用 perror 工具查看具体错误!

超出最大打开文件数限制!ulimit -n 查看系统的最大打开文件数是 65535，不可能超出!那必然是数据库的最大打开文件数超出限制!

在 MySQL 里查看最大打开文件数限制命令：show variables like ‘open_files_limit’;

发现该数值过小，改为 2048，重启 MySQL，应用正常。

处理方法：

repair table ;

chown mysql 权限

清理磁盘中的垃圾数据

十．[Err] 1146 - Table 'performance_schema.session_status' doesn't exist

解决方案：：把用户之前安装的数据库的所有数据清空后在重装后在数据库工具Navicat for MySQL在另行打开运行就没问题；（不建议用这种方法，因为用于有些数据库数据信息需要保留，除非用户原本的数据全部不需要了）

1、用root用户登录mysql数据库 

（1）停止MySQL服务，执行net stop mysql;

（2）在mysql的安装路径下找到配置文件my.ini，

找到[mysqld] 输入：skip-grant-tables，保存

（3）重启mysql服务，net start mysql；

（4）执行mysql -uroot -p，回车，再回车，即可进入mysql数据库；

2、在本机登入mysql后，更改 “mysql” 数据库里的 “user” 表里的 “host” 项，从”localhost”改称’%’。

mysql>use mysql;

查询出数据库的用户名

mysql>select host,user,password from user;

把用户表里面的host项改为“%”

mysql>update user set host = ‘%’ where user =’root’;

mysql>flush privileges;    #刷新用户权限表

mysql>select host,user,password  from user where user=’root’;

3、插入本地登录的用户

mysql>insert into user values(‘localhost’, ‘root’, ”, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’,”,”,”,”,0,0,0,0,”,”);

此时本地连接的用户localhost密码为空

4、修改root密码

用update方式修改root密码正常

mysql> update user set password=password(“123″) where user=”root”;

mysql>flush privileges;

5、退出MySQL，在配置文件中注释：skip-grant-tables，重启mysql服务

6、本地重新连接mysql数据库，输入修改后的密码，连接成功